package dbutils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import util.DBPoolUtils;

public class DBUtilsDemo {
	@Test
	public void insert() throws SQLException {
		Connection c = DBPoolUtils.getConnection();
		String sql = "insert into menu(name,price,spicy,deleteflag) values(?,?,?,false)";
		// 1.创建QueryRunner 
		QueryRunner runner = new QueryRunner();
		// 2.执行
		int row = runner.execute(c, sql, "西红柿炒鸡蛋",12,false);
		System.out.println(row);
		c.close();
	}
	
	@Test
	public void delete() throws SQLException {
		Connection c = DBPoolUtils.getConnection();
		String sql = "delete from menu where id=?";
		// 1.创建QueryRunner 
		QueryRunner runner = new QueryRunner();
		// 2.执行
		int row = runner.execute(c, sql, 5);
		System.out.println(row);
		c.close();
	}
	
	@Test
	public void update() throws SQLException {
		Connection c = DBPoolUtils.getConnection();
		String sql = "update menu set name=?,price=?,spicy=? where id=?";
		// 1.创建QueryRunner 
		QueryRunner runner = new QueryRunner();
		// 2.执行
		int row = runner.execute(c, sql, "角瓜炒鸡蛋",14,true,5);
		System.out.println(row);
		c.close();
	}
	
	@Test
	public void selectById() throws SQLException {
		Connection c = DBPoolUtils.getConnection();
		String sql = "select * from menu where id=?";
		// 1.创建QueryRunner 
		QueryRunner runner = new QueryRunner();
		// 2.执行
		List<Menu> menu = runner.execute(c, sql, new BeanHandler<>(Menu.class),1);
		System.out.println(menu);
		c.close();
	}
	
	@Test
	public void selectByName() throws SQLException{
		Connection c = DBPoolUtils.getConnection();
		String sql = "select * from menu where instr(name,?)>0 and deleteflag=0";
		// 1.创建QueryRunner 
		QueryRunner runner = new QueryRunner();
		// 2.执行
		List me = runner.execute(c, sql, new BeanListHandler<>(Menu.class),"肉");
		System.out.println(me);
		c.close();
	}
}
